﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace Tszy.Unlimited.Data.AdoNet.Sql
{
    /// <summary>
    /// Tszy Db Context 扩展。
    /// </summary>
    public static class TszyDbContextExtensions
    {
        #region execute sql

        /// <summary>
        /// 执行 SQL 脚本。
        /// </summary>
        /// <param name="context">数据访问器上下文。</param>
        /// <param name="sql">SQL 脚本。</param>
        /// <param name="parameters">参数。</param>
        /// <param name="dbTransaction"></param>
        /// <returns>执行成功则返回 True，否则返回 False。</returns>
        public static bool Execute(this ITszyDbContext context, string sql, object parameters = null, IDbTransaction dbTransaction = null)
        {
            var dataProvider = context.DataProvider;

            return dataProvider.Execute(sql, parameters, dbTransaction) > 0;
        }

        /// <summary>
        /// 执行 SQL 脚本查询单个字段的内容。
        /// </summary>
        /// <param name="context">数据访问器上下文。</param>
        /// <param name="sql">SQL 脚本。</param>
        /// <param name="parameters">参数。</param>
        /// <param name="dbTransaction"></param>
        /// <returns>字段的内容。</returns>
        public static object Field(this ITszyDbContext context, string sql, object parameters = null, IDbTransaction dbTransaction = null)
        {
            var dataProvider = context.DataProvider;

            var result = dataProvider.Field(sql, parameters, dbTransaction);

            if (result == null || result == DBNull.Value)
            {
                return null;
            }

            return result;
        }

        /// <summary>
        /// 执行 SQL 脚本查询单个字段的内容。
        /// </summary>
        /// <typeparam name="TSource">字段类型。</typeparam>
        /// <param name="context">数据访问器上下文。</param>
        /// <param name="sql">SQL 脚本。</param>
        /// <param name="parameters">参数。</param>
        /// <param name="dbTransaction"></param>
        /// <returns>字段的内容。</returns>
        public static TSource Field<TSource>(this ITszyDbContext context, string sql, object parameters = null, IDbTransaction dbTransaction = null)
        {
            var dataProvider = context.DataProvider;

            return dataProvider.Field<TSource>(sql, parameters, dbTransaction);
        }

        /// <summary>
        /// 执行 SQL 脚本查询单条数据。
        /// </summary>
        /// <typeparam name="TSource">实体类型。</typeparam>
        /// <param name="context">数据访问器上下文。</param>
        /// <param name="sql">SQL 脚本。</param>
        /// <param name="parameters">参数。</param>
        /// <param name="dbTransaction"></param>
        /// <returns>实体。</returns>
        public static TSource FirstOrDefault<TSource>(this ITszyDbContext context, string sql, object parameters = null, IDbTransaction dbTransaction = null)
        {
            var dataProvider = context.DataProvider;

            var items = dataProvider.Query<TSource>(sql, parameters, dbTransaction) ?? Enumerable.Empty<TSource>();

            return items.Any() ? items.FirstOrDefault() : default;
        }

        /// <summary>
        /// 执行 SQL 脚本查询数据集合。
        /// </summary>
        /// <param name="context">数据访问器上下文。</param>
        /// <param name="type">实体类型。</param>
        /// <param name="sql">SQL 脚本。</param>
        /// <param name="parameters">参数。</param>
        /// <param name="dbTransaction"></param>
        /// <returns>数据集合。</returns>
        public static IEnumerable<object> Query(this ITszyDbContext context, Type type, string sql, object parameters = null, IDbTransaction dbTransaction = null)
        {
            var dataProvider = context.DataProvider;

            return dataProvider.Query(type, sql, parameters, dbTransaction) ?? Enumerable.Empty<object>();
        }

        /// <summary>
        /// 执行 SQL 脚本查询数据集合。
        /// </summary>
        /// <typeparam name="TSource">实体类型。</typeparam>
        /// <param name="context">数据访问器上下文。</param>
        /// <param name="sql">SQL 脚本。</param>
        /// <param name="parameters">参数。</param>
        /// <param name="dbTransaction">事务。</param>
        /// <returns>数据集合。</returns>
        public static IEnumerable<TSource> Query<TSource>(this ITszyDbContext context, string sql, object parameters = null, IDbTransaction dbTransaction = null)
        {
            var dataProvider = context.DataProvider;

            return dataProvider.Query<TSource>(sql, parameters, dbTransaction) ?? Enumerable.Empty<TSource>();
        }

        #endregion execute sql

        #region execute async sql

        /// <summary>
        /// 执行 SQL 脚本。
        /// </summary>
        /// <param name="context">数据访问器上下文。</param>
        /// <param name="sql">SQL 脚本。</param>
        /// <param name="parameters">参数。</param>
        /// <param name="dbTransaction"></param>
        /// <returns>执行成功则返回 True，否则返回 False。</returns>
        public static async Task<bool> ExecuteAsync(this ITszyDbContext context, string sql, object parameters = null, IDbTransaction dbTransaction = null)
        {
            var dataProvider = context.DataProvider;

            return await dataProvider.ExecuteAsync(sql, parameters, dbTransaction) > 0;
        }

        /// <summary>
        /// 执行 SQL 脚本查询单个字段的内容。
        /// </summary>
        /// <param name="context">数据访问器上下文。</param>
        /// <param name="sql">SQL 脚本。</param>
        /// <param name="parameters">参数。</param>
        /// <param name="dbTransaction"></param>
        /// <returns>字段的内容。</returns>
        public static async Task<object> FieldAsync(this ITszyDbContext context, string sql, object parameters = null, IDbTransaction dbTransaction = null)
        {
            var dataProvider = context.DataProvider;

            var result = await dataProvider.FieldAsync(sql, parameters, dbTransaction);

            if (result == null || result == DBNull.Value)
            {
                return null;
            }

            return result;
        }

        /// <summary>
        /// 执行 SQL 脚本查询单个字段的内容。
        /// </summary>
        /// <typeparam name="TSource">字段类型。</typeparam>
        /// <param name="context">数据访问器上下文。</param>
        /// <param name="sql">SQL 脚本。</param>
        /// <param name="parameters">参数。</param>
        /// <param name="dbTransaction"></param>
        /// <returns>字段的内容。</returns>
        public static async Task<TSource> FieldAsync<TSource>(this ITszyDbContext context, string sql, object parameters = null, IDbTransaction dbTransaction = null)
        {
            var dataProvider = context.DataProvider;

            return await dataProvider.FieldAsync<TSource>(sql, parameters, dbTransaction);
        }

        /// <summary>
        /// 执行 SQL 脚本查询单条数据。
        /// </summary>
        /// <typeparam name="TSource">实体类型。</typeparam>
        /// <param name="context">数据访问器上下文。</param>
        /// <param name="sql">SQL 脚本。</param>
        /// <param name="parameters">参数。</param>
        /// <param name="dbTransaction"></param>
        /// <returns>实体。</returns>
        public static async Task<TSource> FirstOrDefaultAsync<TSource>(this ITszyDbContext context, string sql, object parameters = null, IDbTransaction dbTransaction = null)
        {
            var dataProvider = context.DataProvider;

            var items = await dataProvider.QueryAsync<TSource>(sql, parameters, dbTransaction) ?? Enumerable.Empty<TSource>();

            return items.Any() ? items.FirstOrDefault() : default;
        }

        /// <summary>
        /// 执行 SQL 脚本查询数据集合。
        /// </summary>
        /// <param name="context">数据访问器上下文。</param>
        /// <param name="type">实体类型。</param>
        /// <param name="sql">SQL 脚本。</param>
        /// <param name="parameters">参数。</param>
        /// <param name="dbTransaction"></param>
        /// <returns>数据集合。</returns>
        public static async Task<IEnumerable<object>> QueryAsync(this ITszyDbContext context, Type type, string sql, object parameters = null, IDbTransaction dbTransaction = null)
        {
            var dataProvider = context.DataProvider;

            return await dataProvider.QueryAsync(type, sql, parameters, dbTransaction) ?? Enumerable.Empty<object>();
        }

        /// <summary>
        /// 执行 SQL 脚本查询数据集合。
        /// </summary>
        /// <typeparam name="TSource">实体类型。</typeparam>
        /// <param name="context">数据访问器上下文。</param>
        /// <param name="sql">SQL 脚本。</param>
        /// <param name="parameters">参数。</param>
        /// <param name="dbTransaction">事务。</param>
        /// <returns>数据集合。</returns>
        public static async Task<IEnumerable<TSource>> QueryAsync<TSource>(this ITszyDbContext context, string sql, object parameters = null, IDbTransaction dbTransaction = null)
        {
            var dataProvider = context.DataProvider;

            return await dataProvider.QueryAsync<TSource>(sql, parameters, dbTransaction) ?? Enumerable.Empty<TSource>();
        }

        #endregion execute sql
    }
}
